﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Working with Statements and Result Sets</title>
    
    <link rel="stylesheet" type="text/css" href="../local/Classic.css">
      
    </link>
    
    <script src="../local/script.js">
      
    </script><script src="../local/script_main.js">&amp;nbsp;</script>
  </head>
  <body>
    <!--Topic built:03/05/2009 21:01:45-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="headerBold">Working with Statements and Result Sets</span>
          </td>
          <td align="right">
            
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      
        
        
		<font color="DarkGray"> </font> <p /> <p />
	
        <div id="introductionSection" class="section">
    <p>When you work with the Microsoft SQL Server JDBC Driver and the <b>Statement</b> and <b>ResultSet</b> objects that it provides, there are several techniques that you can use to improve the performance and reliability of your applications. </p>
  </div><h1 class="heading">Use the Appropriate Statement Object</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">When you use one of the JDBC driver <b>Statement</b> objects, such as the <a href="ec24963c-8b51-4838-91e9-1fbfa2347451.htm">SQLServerStatement</a>, <a href="a8481c06-fbba-432b-8c69-4f4619c20ad4.htm">SQLServerPreparedStatement</a>, or the <a href="30710a63-c05d-47d9-9cf9-c087a1c76373.htm">SQLServerCallableStatement</a> object, make sure that you are using the appropriate object for the job.</p>
      <ul xmlns=""><li>
          If you do not have OUT parameters, you do not need to use the <b>SQLServerCallableStatement</b> object. Instead, use the <b>SQLServerStatement</b> or the <b>SQLServerPreparedStatement</b> object.<br />
        </li><li>
          If you do not intend to execute the statement more than once, or do not have IN or OUT parameters, you do not need to use the <b>SQLServerCallableStatement</b> or the <b>SQLServerPreparedStatement</b> object. Instead, use the <b>SQLServerStatement</b> object.<br />
        </li></ul>
    </content></div><h1 class="heading">Use the Appropriate Concurrency for ResultSet Objects</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Do not ask for updatable concurrency when you create statements that produce result sets unless you actually intend to update the results. The default forward-only, read-only cursor model is fastest for reading small result sets.</p>
    </content></div><h1 class="heading">Limit the Size of Your Result Sets</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Consider using the <a href="cccc0667-589b-4655-8ea8-14ae8b2eb9dc.htm">setMaxRows</a> method (or SET ROWCOUNT or SELECT TOP N SQL syntax) to limit the number of rows returned from potentially large result sets. If you must deal with large result sets, consider using an adaptive response buffering by setting the connection string property responseBuffering=adaptive, which is the default mode. This approach allows the application to process large result sets without requiring the server-side cursors and minimizes the application memory usage. For more information, see <a href="92d4e3be-c3e9-4732-9a60-b57f4d0f7cb7.htm">Using Adaptive Buffering</a>.</p>
    </content></div><h1 class="heading">Use the Appropriate Fetch Size</h1><div id="sectionSection3" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">For read-only server cursors, the tradeoff is round trips to the server versus the amount of memory used in the driver. For updatable server cursors, the fetch size also influences the sensitivity of the result set to changes and concurrency on the server. Updates to rows within the current fetch buffer are not visible until an explicit <a href="048fe245-157f-4fd8-be75-ce54b83e02b3.htm">refreshRow</a> method is issued or until the cursor leaves the fetch buffer. Large fetch buffers will have better performance (fewer server round trips) but are less sensitive to changes and reduce concurrency on the server if CONCUR_SS_SCROLL_LOCKS (1009) is used. For maximum sensitivity to changes, use a fetch size of 1. However, note that this will incur a round trip to the server for every row fetched.</p>
    </content></div><h1 class="heading">Use Streams for Large IN Parameters</h1><div id="sectionSection4" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">Use streams or BLOBs and CLOBs that are incrementally materialized to handle updating large column values or sending large IN parameters. The JDBC driver "chunks" these to the server in multiple round trips, allowing you to set and update values larger than what will fit in memory.</p>
    </content></div><span id="seeAlsoSpan"><h1 class="heading">See Also</h1></span><div id="seeAlsoSection" class="section" name="collapseableSection"><a href="e1592499-b87b-45ee-bab8-beaba8fde841.htm">Improving Performance and Reliability with the JDBC Driver</a><br /><br /></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]-->
      <div id="footer" class="section">
        
		<hr />
		
		<span id="fb" class="feedbackcss">
			
			
		</span>
		
		<a href="9bad553b-9e70-4696-8499-2e35f772a1e0.htm">
			
			© 2009 Microsoft Corporation. All rights reserved.
		</a>
 	
	
      </div>
    </div>
  </body>
</html>